|
A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a ''self-join''.A programmer writes a JOIN statement to identify the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table.==Sample tables== Relational databases are usually normalized to eliminate duplication of information such as when objects have one-to-many relationships. For example, a Department may be associated with a number of Employees. Joining separate tables for Department and Employee effectively creates another table which combines the information from both tables. This is at some expense in terms of the time it takes to compute the join. While it is also possible to simply maintain a denormalized table if speed is important, duplicate information may take extra space, and add the expense and complexity of maintaining data integrity if data which is duplicated later changes. All subsequent explanations on join types in this article make use of the following two tables. The rows in these tables serve to illustrate the effect of different types of joins and join-predicates. In the following tables the DepartmentID column of the Department table (which can be designated as Department.DepartmentID) is the primary key, while Employee.DepartmentID is a foreign key.Note: In the Employee table above, the employee "Williams" has not been assigned to any department yet. Also, note that no employees are assigned to the "Marketing" department. This is the SQL statement to create the aforementioned tables. CREATE TABLE department ( DepartmentID INT, DepartmentName VARCHAR(20) ); CREATE TABLE employee ( LastName VARCHAR(20), DepartmentID INT ); INSERT INTO department VALUES(31, 'Sales'); INSERT INTO department VALUES(33, 'Engineering'); INSERT INTO department VALUES(34, 'Clerical'); INSERT INTO department VALUES(35, 'Marketing'); INSERT INTO employee VALUES('Rafferty', 31); INSERT INTO employee VALUES('Jones', 33); INSERT INTO employee VALUES('Heisenberg', 33); INSERT INTO employee VALUES('Robinson', 34); INSERT INTO employee VALUES('Smith', 34); INSERT INTO employee VALUES('Williams', NULL); 抄文引用元・出典: フリー百科事典『 ウィキペディア（Wikipedia）』 ■ウィキペディアで「Join (SQL)」の詳細全文を読む スポンサード リンク
|